package com.ecsolutions.dao;

import com.ecsolutions.entity.User_Entity;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.HashMap;
import java.util.List;

/**
 * Created by ecs on 2017/8/18.
 */
@Repository
public interface User_DAO {

    @Insert("insert into tblusers(" +
            "userid," +
            "username," +
            "passwrd," +
            "email," +
            "branch," +
            "businesamoun," +
            "considerationamount," +
            "auditamount," +
            "organizationcode," +
            "pauditamt" +
            ") " +
            "values (" +
            "#{userid,jdbcType=VARCHAR}," +
            "#{username,jdbcType=VARCHAR}," +
            "#{passwrd,jdbcType=VARCHAR}," +
            "#{email,jdbcType=VARCHAR}," +
            "#{branch,jdbcType=VARCHAR}," +
            "#{businesamoun,jdbcType=VARCHAR}," +
            "#{considerationamount,jdbcType=VARCHAR}," +
            "#{auditamount,jdbcType=VARCHAR}," +
            "#{organizationcode,jdbcType=VARCHAR}," +
            "#{pauditamt,jdbcType=VARCHAR}" +
            ")")
    @ResultType(Boolean.class)
    public boolean save(User_Entity entity);

    @Select("SELECT userid from tblusers")
    @ResultType(HashMap.class)
    public List<HashMap<String,String>> getAllUserId();

    @Select("SELECT COUNT(*) FROM tblusers")
    @ResultType(Integer.class)
    Integer countAll();

    @Select("SELECT COUNT(*) FROM tblusers WHERE userid=" + "#{userid,jdbcType=VARCHAR}")
    @ResultType(Integer.class)
    Integer countOne(String userid);

    //     WHERE custcode="+ "#{CustCode,jdbcType=VARCHAR}
    @Select("SELECT * FROM tblusers Order By userid Asc")
    @ResultType(User_Entity.class)
    List<User_Entity> findAll();

    @Select("SELECT * FROM tblusers Where branch=#{branch} Order By userid Asc")
    @ResultType(User_Entity.class)
    List<User_Entity> findByBranch(String branch);

    @Select("SELECT * FROM tblusers Where userid like concat(concat('%',#{UserIdCode}),'%') Order By userid Asc")
    @ResultType(User_Entity.class)
    List<User_Entity> findByUserId(String UserIdCode);

    @Select("SELECT * FROM tblusers Where TRIM(userid)= #{UserIdCode} And rownum=1")
    @ResultType(User_Entity.class)
    User_Entity getByUserId(String UserIdCode);

    @Select("SELECT * FROM tblusers Where TRIM(username)= #{UserName} And rownum=1")
    @ResultType(User_Entity.class)
    User_Entity getByUserName(String UserName);

    @Select("SELECT * FROM tblusers Where userid like concat(concat('%',#{param1}),'%') AND branch= #{param2} Order By userid Asc")
    @ResultType(User_Entity.class)
    List<User_Entity> findByUserIdandBranch(String UserId, String branch);

    @Select("SELECT COUNT(*) FROM tblusers Where branch= #{branch}")
    @ResultType(Integer.class)
    Integer countByBranch(String branch);

    @Select("SELECT COUNT(*) FROM tblusers Where userid like concat(concat('%',#{UserId}),'%')")
    @ResultType(Integer.class)
    Integer countByUserId(String UserId);

    @Select("SELECT COUNT(*) FROM tblusers Where userid like concat(concat('%',#{param1}),'%') AND branch= #{param2}")
    @ResultType(Integer.class)
    Integer countByUserIdandBranch(String UserId, String branch);

    @Update("Update tblusers " +
            "SET " +
            "username=" + "#{username,jdbcType=VARCHAR}," +
            "passwrd=" + "#{passwrd,jdbcType=VARCHAR}," +
            "email=" + "#{email,jdbcType=VARCHAR}," +
            "branch=" + "#{branch,jdbcType=VARCHAR}," +
            "businesamoun=" + "#{businesamoun,jdbcType=VARCHAR}," +
            "considerationamount=" + "#{considerationamount,jdbcType=VARCHAR}," +
            "auditamount=" + "#{auditamount,jdbcType=VARCHAR}," +
            "organizationcode= " + "#{organizationcode,jdbcType=VARCHAR}," +
            "pauditamt= " + "#{pauditamt,jdbcType=VARCHAR}" +
            "  Where userid=" + "#{userid,jdbcType=VARCHAR}"
    )
    @ResultType(Boolean.class)
    boolean update(User_Entity entity);

    @Delete("DELETE FROM tblusers Where userid=" + "#{userid,jdbcType=VARCHAR}")
    @ResultType(Boolean.class)
    boolean delete(String userid);

    @Select("SELECT NVL(MAX(userid),0)+1 FROM tblusers")
    @ResultType(String.class)
    String getMaxId();

    //通过userid获取用户信息
    @Select("SELECT * FROM tblusers Where TRIM(userid)= #{userid} And rownum=1")
    @ResultType(User_Entity.class)
    User_Entity getInfoByUserid(String userid);
}
